#!coding:utf8
'''
Created on Aug 23, 2013

@author: join
'''
from lottery_jx.models import Account, TicketLoan,TicketConstant,SevenColor,ThreeD,FiveBall,SixPlusOne,\
    Trade
import xlwt

def account_info():
    wb = xlwt.Workbook()
    sheet_name = 'pandian'
    ws = wb.add_sheet(sheet_name)
    
    from xlwt import Font,Borders,Alignment,XFStyle
    fnt = Font()
    fnt.name = 'Arial'
    fnt.colour_index = 0
    fnt.bold = True
    
    borders = Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    
    al = Alignment()
    al.horz = Alignment.HORZ_CENTER
    al.vert = Alignment.VERT_CENTER
    style = XFStyle()
    style.font = fnt
    style.alignment = al
    style2 = XFStyle()
    style2.alignment = al

    ws.write(0, 0, 'No.',style)
    ws.write(0, 1, u'帐号',style)
    ws.write(0, 2, u'充值',style)
    ws.write(0, 3, u'返奖',style)
    ws.write(0, 4, u'提成',style)
    ws.write(0, 5, u'总销售',style)
    ws.write(0, 6, u'投币销售',style)
    ws.write(0, 7, u'提现',style)
    ws.write(0, 8, u'理论额度',style)
    ws.write(0, 9, u'实际额度',style)
    ws.write(0, 10, u'差额',style)
    
    rownum = 1
    
    accounts = Account.objects.filter(deleted=False)
    for account in accounts:
        #双色球返奖
        loan_prizes = TicketLoan.objects.filter(prizemoney__isnull=False,tradeId__accountid=account)
        loan_prize_money = sum([l.prizemoney for l in loan_prizes])
        #时时彩返奖
        constant_prizes = TicketConstant.objects.filter(prizemoney__isnull=False,tradeId__accountid=account)
        constant_prize_money = sum([l.prizemoney for l in constant_prizes])
        #七乐彩返奖
        sevencolor_prizes = SevenColor.objects.filter(prizemoney__isnull=False,tradeId__accountid=account)
        sevencolor_prize_money = sum([l.prizemoney for l in sevencolor_prizes])
        #3D彩返奖
        threeD_prizes = ThreeD.objects.filter(prizemoney__isnull=False,tradeId__accountid=account)
        threeD_prize_money = sum([l.prizemoney for l in threeD_prizes])
        #3D彩返奖
        fiveBall_prizes = FiveBall.objects.filter(prizemoney__isnull=False,tradeId__accountid=account)
        fiveBall_prize_money = sum([l.prizemoney for l in fiveBall_prizes])
        #东方6+1彩返奖
        sixPlusOne_prizes = SixPlusOne.objects.filter(prizemoney__isnull=False,tradeId__accountid=account)
        sixone_prize_money = sum([l.prizemoney for l in sixPlusOne_prizes])
        #总返奖
        total_prize_money = loan_prize_money+constant_prize_money+sevencolor_prize_money+threeD_prize_money+fiveBall_prize_money+sixone_prize_money
        
        trades_xiaoshou = Trade.objects.filter(accountid=account,tradeType=1).exclude(status=0)
        money_xiaoshou = sum([t.tradeAmount for t in trades_xiaoshou])
        
        trades_toubi = trades_xiaoshou.exclude(tradeMethod=0)
        money_toubi = sum([t.tradeAmount for t in trades_toubi])
        
        ratio = account.commissionRatio if account.commissionRatio else 0.07
        money_xiaoshou = money_xiaoshou if money_xiaoshou else 0.0
        ticheng = money_xiaoshou*ratio
        
        trades_chongzhi = Trade.objects.filter(accountid=account,tradeType=0).exclude(tradeTime__range=('2013-8-18 19:00:00',"2013-8-18 19:15:00"))
        money_chongzhi = sum([t.tradeAmount for t in trades_chongzhi])
        
        trades_tixian = Trade.objects.filter(accountid=account,tradeType=2)
        money_tixian = sum([t.tradeAmount for t in trades_tixian])
        
        lilun = (money_chongzhi+ticheng+total_prize_money-money_tixian-money_toubi)
        
        ws.write(rownum,0,rownum,style2)
        ws.write(rownum,1,account.account,style2)
        ws.write(rownum,2,money_chongzhi,style2)
        ws.write(rownum,3,total_prize_money,style2)
        ws.write(rownum,4,ticheng,style2)
        ws.write(rownum,5,money_xiaoshou,style2)
        ws.write(rownum,6,money_toubi,style2)
        ws.write(rownum,7,money_tixian,style2)
        ws.write(rownum,8,(money_chongzhi+ticheng+total_prize_money-money_tixian-money_toubi),style2)
        ws.write(rownum,9,account.balance,style2)
        ws.write(rownum,10,'%.2f'%(lilun-account.balance),style2)
        rownum += 1
    wb.save("./pandian5.xls")

if __name__ == "__main__":
    account_info()
    